<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="cn.edu.scau.yangchunmei.bean.book"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.util.ArrayList"%>
<%@ page import="java.util.List"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>显示图书列表</title>
<style type="text/css">td,th {	padding: 5px;}
</style>
</head>
<body>
<% 
Class.forName("com.mysql.jdbc.Driver");

	String url = "jdbc:mysql://localhost:3306/jsp";
	String username = "root";
	String password = "root";
	Connection conn = DriverManager.getConnection(url, username,password);
	Statement stmt = conn.createStatement();		// 获取Statement
	String sql = "select * from tb_book";			// 添加图书信息的SQL语句	
	ResultSet rs = stmt.executeQuery(sql);			// 执行查询	
	List<book> list = new ArrayList<>();		// 实例化List对象	
	while(rs.next()){								// 光标向后移动，并判断是否有效
		book book1 = new book();					// 实例化Book对象
		book1.setId(rs.getInt("id"));				// 对id属性赋值
		book1.setName(rs.getString("name"));		// 对name属性赋值
		book1.setPrice(rs.getDouble("price"));		// 对price属性赋值
		book1.setBookCount(rs.getInt("bookCount"));	// 对bookCount属性赋值
		book1.setAuthor(rs.getString("author"));		// 对author属性赋值
		list.add(book1); 							// 将图书对象添加到集合中
	}
	request.setAttribute("list", list); 	
	%>
	
		<h2>所有图书信息</h2>

	<table width="98%" border="0" align="center" cellpadding="0"
		cellspacing="1" bgcolor="#666666">
		<tr>
			<th bgcolor="#FFFFFF">ID</th>
			<th bgcolor="#FFFFFF">图书名称</th>
			<th bgcolor="#FFFFFF">价格</th>
			<th bgcolor="#FFFFFF">数量</th>
			<th bgcolor="#FFFFFF">作者</th>
		</tr>
		<%
		if (list == null || list.size() < 1) {
				out.print("<tr><td bgcolor='#FFFFFF' colspan='5'>没有任何图书信息！</td></tr>");
			} else {
				// 遍历图书集合中的数据
				for (book book1 : list) {
		%>
		<tr align="center">
			<td bgcolor="#FFFFFF" ><%=book1.getId()%></td>
			<td bgcolor="#FFFFFF"><%=book1.getName()%></td>
			<td bgcolor="#FFFFFF"><%=book1.getPrice()%></td>
			<td bgcolor="#FFFFFF"><%=book1.getBookCount()%></td>
			<td bgcolor="#FFFFFF"><%=book1.getAuthor()%></td>
		</tr>
		<%
			}
		}
		%>
	</table>
</body>
</html>